"""
对没用趋势数据的虚拟机进行记录，虚拟机恢复后通过该方法来校验恢复情况。
"""
import pymysql.cursors
from openpyxl import load_workbook
from openpyxl import Workbook

c = pymysql.connect(host='*',
                    user='*',
                    password='',
                    db='zabbix',
                    charset='utf8mb4',
                    cursorclass=pymysql.cursors.DictCursor) # DictCursor 返回的是一个字典对象。
output_path = 'f:\\out.txt'


class SingleExcel:
    def __init__(self, read_path=None, set_read_sheet=None, read_column=None, write_path=None, set_write_sheet=None):
        self.read_path = read_path if read_path else None
        self.write_path = write_path if write_path else None
        self.read_mem_sheet = None
        if self.read_path:
            self.lwb = load_workbook(filename=self.read_path)
            self.read_sheet = self.lwb[set_read_sheet]
            self.read_mem_sheet = self.read_all_sheet(read_column)

        if self.write_path:
            self.wb = Workbook()
            self.write_wb = self.wb.create_sheet(title=set_write_sheet, index=0)
            self.write_path = write_path

    def read_all_sheet(self, _read_column):
        sheets = {}
        for row in range(1, 9999):
            row_sheets = {}
            for column in _read_column:
                c_r = column + str(row)
                v = self.read_sheet[c_r].value
                if v:
                    row_sheets.setdefault(c_r, v)
                # row_sheets[c_r] = self.read_sheet[c_r].value
            if row_sheets:
                sheets.update(row_sheets)
            else:
                break
        return sheets

    def write_all_sheet(self, sheets: dict, column: str):
        for key in sheets:
            self.write_wb[key] = sheets[key]
        self.wb.save(filename=self.write_path)

class SQL:
    def __init__(self):
        self.hosts_data = {'total': {}, 'nodata': {}, 'data': {}}
        pass

    def query(self, sql):
        _hostid = []
        with c.cursor() as cc:
            rows = cc.execute(sql)
            for line in range(rows):
                data = cc.fetchone()
                _hostid.append(data)
        return _hostid

    def filter(self, item_key, trends_table):
        host_sql = 'select i.hostid, h.name, ii.ip, i.itemid from items i ' \
                   'join hosts h on h.hostid = i.hostid ' \
                   'join interface ii on ii.hostid = h.hostid ' \
                   'where i.key_ = "{}"'.format(item_key)
        history_sql = 'select * from {} where itemid = {} order by clock desc limit 1'
        # 返回一个数组，数组中包含n个字典数据包含虚拟机数据，数据包含：hostid，name，ip，itemid。
        hosts = self.query(host_sql)

        for host in hosts:
            # 将所有虚拟机保存进
            self.hosts_data['total'].setdefault(host['hostid'], [host['name'], host['ip'], host['itemid']])
            trend_data = self.query(history_sql.format(trends_table, host['itemid']))
            if trend_data:
                for rr in trend_data:
                    if rr['clock'] < 1583884253:
                        """ 最后一条趋势记录在3月9号12点之前，表示该数据已经有几个小时没用更新。"""
                        self.hosts_data['nodata'].setdefault(host['hostid'], [host['name'], host['ip'], host['itemid']])
                        pass
                    else:
                        # 这里是所有有历史数据的虚拟机。
                        self.hosts_data['data'].setdefault(host['hostid'], [host['name'], host['ip'], host['itemid']])
                        pass
            else:
                """ 查询趋势数据没用任何一条数据记录。"""
                self.hosts_data['nodata'].setdefault(host['hostid'], [host['name'], host['ip'], host['itemid']])
        # 打印每一种监控项包含的total所有虚拟机，nodata无数据虚拟机，和data有数据虚拟机。
        print(len(self.hosts_data['total']), len(self.hosts_data['nodata']), len(self.hosts_data['data']))


class TrendExcel(SingleExcel):
    """
    对趋势虚拟机进行处理，获取某个趋势所有虚拟机、有趋势数据虚拟机、无趋势虚拟机。
    """
    def __init__(self, read_path=None, set_read_sheet=None, write_path=None, set_write_sheet=None, read_column=None):
        super().__init__(read_path=read_path, set_read_sheet=set_read_sheet, write_path=write_path, set_write_sheet=set_write_sheet, read_column=read_column)

    def read_all_sheet(self, _read_column):
        sheets = {}
        for row in range(1, 9999):
            row_sheets = []
            for column in _read_column:
                r_c = column + str(row)
                row_sheets.append(self.read_sheet[r_c].value)
            itemid = row_sheets[0]
            sheets[itemid] = row_sheets[1:]
            if row_sheets:
                sheets.setdefault(itemid, row_sheets)
            else:
                break
        return sheets

    def write_all_sheet(self, sheets: dict, column: str):
        """
        对几个字典内容保存到一个工作簿中。
        :param sheets:多个字典。
        :param column:一个字典保存对应哪几列。
        """
        row = 0
        for sheet in sheets.keys():
            if sheet:
                row += 1
                for n, col in enumerate(column):
                    c_r = col + str(row)
                    if n == 0:
                        self.write_wb[c_r] = sheet
                    else:
                        self.write_wb[c_r] = sheets[sheet][n - 1]
        self.wb.save(filename=self.write_path)


def check_is_trends():
    """
    对多个监控项导出他们合计的虚拟机、有趋势数据的虚拟机和无趋势数据的虚拟机。
    """
    item_to_table = {
        # 'system.localtime': 'trends_uint',
        # 'system.uptime': 'trends_uint',
        # 'vm.memory.size[pavailable]': 'trends',
        # 'system.cpu.load[all,avg1]': 'trends',
        # 'sockstat.sockets': 'trends_uint',
        'vfs.fs.size[/,total]': 'trends_uint',
        # 'system.swap.size[,pused]': 'trends'
    }
    data = []
    for key in item_to_table.keys():
        d = SQL()
        data.append(d)
        d.filter(key, item_to_table[key])

    _read_path = 'f:\\nodata\\trends\\3-18(vfs.fs.size.total).xlsx'
    excel = TrendExcel(write_path=_read_path, set_write_sheet='vfs.fs.size.total')
    print('total', len(data[0].hosts_data['total']), data[0].hosts_data['total'])
    print('data', len(data[0].hosts_data['data']), data[0].hosts_data['data'])
    print('nodata', len(data[0].hosts_data['nodata']), data[0].hosts_data['nodata'])
    excel.write_all_sheet(data[0].hosts_data['total'], 'abcd')
    excel.write_all_sheet(data[0].hosts_data['data'], 'fghi')
    excel.write_all_sheet(data[0].hosts_data['nodata'], 'klmn')


class IPExcel(SingleExcel):
    """
    对IP表导出的数据进行转换，主要是转换IP地址，将十进制表示的IP地址转变成真实的IP地址格式。
    """
    def __init__(self, read_path=None, set_read_sheet=None, write_path=None, set_write_sheet=None, read_column=None):
        super().__init__(read_path=read_path, set_read_sheet=set_read_sheet, write_path=write_path, set_write_sheet=set_write_sheet, read_column=read_column)

    def int_to_ip(self, num):
        """
        对十进制数转换成IP地址。
        :param num: 十进制。
        :return: IP地址。
        """
        ip = ''
        for i in (24, 16, 8):
            ip += str(num >> i & 0xff)
            ip += '.'
        ip += str(num & 0xff)
        return ip

    def write_all_sheet(self, sheets: dict, column: str):
        for key in sheets:
            key_value = sheets[key]
            if 'a' in key:
                self.write_wb[key] = self.int_to_ip(key_value)
            else:
                self.write_wb[key] = key_value
        self.wb.save(filename=self.write_path)


def convert_ip():
    """
    对IP表数据转换成IP地址。
    :return:
    """
    read_path = 'f:\\nodata\\ip_pwd.xlsx'
    ipexcel = IPExcel(read_path=read_path, set_read_sheet='ip_pwd',
                      write_path=read_path, set_write_sheet='ip', read_column='abc')
    ipexcel.write_all_sheet(ipexcel.read_mem_sheet, 'abc')


def get_pwd():
    nodata_path = 'F:\\nodata\\trends\\3-12(system.localtime).xlsx'
    _nodata = TrendExcel(read_path=nodata_path, set_read_sheet='system.localtime', read_column='lm')
    ip_path = 'F:\\nodata\\ip_pwd.xlsx'
    _ip_pwd = TrendExcel(read_path=ip_path, set_read_sheet='ip', read_column='bac')
    nodata_ab = _nodata.read_mem_sheet
    all_name_ip_pwd_dict = _ip_pwd.read_mem_sheet

    all_ip_name_pwd_dict = {all_name_ip_pwd_dict[key][0]: [key, all_name_ip_pwd_dict[key][1]]
                            for key in all_name_ip_pwd_dict}
    # all_ip_pwd_dict = {all_name_ip_pwd_dict[key][0]: all_name_ip_pwd_dict[key][1] for key in all_name_ip_pwd_dict}

    nodata_a_inter_all_set = set(nodata_ab).intersection(set(all_name_ip_pwd_dict))
    nodata_a_inter_all_dict = {key: all_name_ip_pwd_dict[key] for key in nodata_a_inter_all_set}

    nodata_b_set = set(nodata_ab) - nodata_a_inter_all_set
    nodata_b_dict = {nodata_ab[key][0]: key for key in nodata_b_set}

    nodata_b_inter_all_set = set(nodata_b_dict).intersection(all_ip_name_pwd_dict)
    nodata_b_inter_all_dict = {all_ip_name_pwd_dict[key][0]: [key, all_ip_name_pwd_dict[key][1]]
                               for key in nodata_b_inter_all_set}

    print('nodata_a_inter_all_dict', len(nodata_a_inter_all_dict), nodata_a_inter_all_dict)
    print('nodata_b_inter_all_dict', len(nodata_b_inter_all_dict), nodata_b_inter_all_dict)
    sum = {}
    sum.update(nodata_a_inter_all_dict)
    sum.update(nodata_b_inter_all_dict)
    print('sum', len(sum), sum)
    txt_path = 'f:\\nodata\\trends\\ip_pwd.txt'
    with open(txt_path, 'w') as w:
        with open('f:\\nodata\\trends\\ip_pwd_2.txt', 'w') as ww:
            for key in sum:
                if key is not None:
                    txt = ''
                    values = sum[key]
                    if isinstance(values, list):
                        for n, v in enumerate(values):
                            if n == 0:
                                txt += v + ' '
                                txt += 'ansible_ssh_host=' + v + ' '
                            elif n == 1:
                                temp = v.split('\n')
                                for string in temp:
                                    if 'root/' in string or 'admin/' in string:
                                        user, pwd = string.split('/')[0:2]
                                        txt += 'ansible_ssh_user=' + user + ' ansible_ssh_pass=' + pwd + '\n'
                                        if 'root' in string:
                                            w.write(txt)
                                        elif 'admin' in string:
                                            ww.write(txt)


if __name__ == '__main__':
    check_is_trends()